package com.hsq.jdbc.utils;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.jupiter.api.Test;

import java.sql.*;
import java.util.List;

public class DBUtils_Use {

    //使用Apache-DBUtils 工具类 + druid 完成对表的crud操作
    @Test
    public void testQueryMany() throws SQLException {//返回结果是多行数据
        //1.得到连接
        Connection connection = JDBCUtilsByDruid.getConnection();
        //2.使用DBUtils 类和接口，先引入DBUtils相关的jar包
        //3.创建QueryRunner
        QueryRunner queryRunner = new QueryRunner();
        //4.使用相关方法，返回ArrayList结果集
        String sql = "select * from student where id >= ?";
        //也可以得到部分属性
//        String sql = "select id, name from student where id >= ?";
        //query方法就是执行sql语句，得到resultSet-----封装到---> ArrayList集合中,然后返回集合
        //connection：连接
        //sql : 执行sql语句
        //new BeanListHandler<>()Student.class): 在将resultSet -> Student对象 -> 封装到ArrayList
        //底层机制是使用反射机制去获取Student类的属性，然后进行封装
        //1 是传入的参数，该参数是可变参数Object... params 可以是多个值
        //底层的得到的ResultSet会在query关闭，创建的preparedStatement
        /*public <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
            if (conn == null) {
                throw new SQLException("Null connection");
            } else if (sql == null) {
                throw new SQLException("Null SQL statement");
            } else if (rsh == null) {
                throw new SQLException("Null ResultSetHandler");
            } else {
                Statement stmt = null;
                ResultSet resultSet = null;
                T result = null;

                try {
                    if (params != null && params.length > 0) {
                        PreparedStatement ps = this.prepareStatement(conn, sql);
                        stmt = ps;
                        this.fillStatement(ps, params);
                        resultSet = this.wrap(ps.executeQuery());
                    } else {
                        stmt = conn.createStatement();
                        resultSet = this.wrap(((Statement)stmt).executeQuery(sql));
                    }

                    result = rsh.handle(resultSet);
                } catch (SQLException var12) {
                    SQLException e = var12;
                    this.rethrow(e, sql, params);
                } finally {
                    this.closeQuietly(resultSet);
                    this.closeQuietly((Statement)stmt);
                }

                return result;
            }*/
        List<Student> list =
                queryRunner.query(connection, sql, new BeanListHandler<>(Student.class), 1);
        for (Student student : list) {
            System.out.println(student);
        }

        //释放资源
        JDBCUtilsByDruid.close(null, null, connection);
    }

    @Test
    public void testQuerySingle() throws SQLException {
        Connection connection = JDBCUtilsByDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        //执行相关方法返回单个对象
        String sql = "select * from student where id = ?";
        Student student = queryRunner.query(connection, sql, new BeanHandler<>(Student.class), 2);
        System.out.println(student);
        JDBCUtilsByDruid.close(null, null, connection);
    }

    @Test
    public void testScalar() throws SQLException {
        Connection connection = JDBCUtilsByDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select name from student where id = ?";
        //因为返回的是一个对象(列)，使用的handler就是ScalarHandler
        Object obj = queryRunner.query(connection, sql, new ScalarHandler(), 1);
        System.out.println(obj);
        JDBCUtilsByDruid.close(null, null, connection);
    }

    @Test
    public void testDML() throws SQLException {
        //1.得到连接
        Connection connection = JDBCUtilsByDruid.getConnection();
        //2.使用DBUtils 类和接口，先引入DBUtils相关的jar包
        //3.创建QueryRunner
        QueryRunner queryRunner = new QueryRunner();
        //4.使用相关方法，返回ArrayList结果集
//        String sql = "select * from student where id >= ?";
        //也可以得到部分属性
//        String sql = "insert into student values (null, ?, ?, ?, ?)";
//        String sql = "update student set name = ? where id = ?";
        String sql = "delete from student where id = ?";
//        返回的值是受影响的行数
//        int affectedRow = queryRunner.update(connection, sql, "张韶涵", "女", "1990-10-10", "123456");
//        int affectedRow = queryRunner.update(connection, sql, "蔡依林", 3);
        int affectedRow = queryRunner.update(connection, sql, 30);
        System.out.println(affectedRow > 0 ? "执行成功": "执行没有影响该表");
        JDBCUtilsByDruid.close(null, null, connection);
    }
}
